/**
 * Copyright (C) 2011 Computer Science Department, Loei Rajabhat University
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
 */

package th.ac.lru.cs.ktd.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import th.ac.lru.cs.ktd.beans.Vocabulary;
import th.ac.lru.cs.ktd.sqlite.SQLite;

public class VocabularyDao {
	
	private static VocabularyDao mDaoInstance;
	
	private VocabularyDao() {
	}
	
	public static VocabularyDao getInstance() {
		if (mDaoInstance == null) {
			mDaoInstance = new VocabularyDao();
			return mDaoInstance;
		}
		return mDaoInstance;
	}
	
	public ArrayList<Vocabulary> findVocabularyByVocab(String vocab) {
        String sql = "SELECT * FROM AllKoreanThai WHERE vocabulary LIKE '%" + vocab + "%'";
        return executeSearch(sql, SQLite.VOCAB_SEARCH);
	}
	
	public ArrayList<Vocabulary> findVocabularyByPronounce(String vocab) {
		String sql = "SELECT * FROM KoreanThai WHERE vocapronounce LIKE '%" + vocab + "%'";
        return executeSearch(sql, SQLite.KOREAN_THAI);
	}
	
	public ArrayList<Vocabulary> getVocabularyByKoreanCharacter(int fromIndex, int toIndex) {
		String sql = "SELECT * FROM KoreanThai WHERE id BETWEEN '" + fromIndex + "' AND '" + toIndex + "'";
        return executeSearch(sql, SQLite.KOREAN_THAI);
            
	}
	
	public ArrayList<Vocabulary> getVocabularyByThaiCharacter(String startWith) {
		String sql = "SELECT * FROM ThaiKorean WHERE vocabulary LIKE '" + startWith + "%'";
		return executeSearch(sql, SQLite.THAI_KOREAN);
	}
	
	private ArrayList<Vocabulary> executeSearch(String sql, String connectionString) {
        Connection connection 	= null;
        Statement statement 	= null;
        try {
        	connection = SQLite.getConnection(connectionString);
            
            statement = connection.createStatement();
            statement.setQueryTimeout(50);

            ResultSet resultSet = statement.executeQuery(sql);
            ArrayList<Vocabulary> vocabularies = new ArrayList<Vocabulary>();
            
            while (resultSet.next()) {
                Vocabulary vocb = new Vocabulary();
                vocb.setId(resultSet.getInt("Id"));
                vocb.setVocabulary(resultSet.getString("vocabulary"));
                vocb.setVocapronounce(resultSet.getString("vocapronounce"));
                vocb.setVocamean(resultSet.getString("vocamean"));
                vocb.setSample(resultSet.getString("sample"));
                vocb.setSampronounce(resultSet.getString("sampronounce"));
                vocb.setSammean(resultSet.getString("sammean"));
                vocabularies.add(vocb);
            }
            return vocabularies;

        } catch (SQLException ex) {
            Logger.getLogger(VocabularyDao.class.getName()).log(Level.SEVERE, null, ex);
            
        } finally {
            try {
				if (statement != null) {
					statement.close();	
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
            try {
            	if (!connection.isClosed()) {
					connection.close(); 
				}
			} catch (SQLException e) {
					e.printStackTrace();
			}
        }
		return null;		
	}
}
